import openpyxl
import tools_pmc

wb_mc = openpyxl.load_workbook(
    r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\data\2025.03.21\物控报表2025.03.21.xlsx")
ws_mc = wb_mc.worksheets[0]

dict_mc_sn = {}
for i in range(2, ws_mc.max_row + 1):
    dict_mc_sn[ws_mc.cell(row=i, column=1).value] = i

wb_年度预测 = openpyxl.load_workbook(
    r"\\192.168.70.101\19计划管理部\01.计划\3，物料需求\9，2025年年度预测\2025年度预测成品需求明细表.xlsx")
ws_年度预测 = wb_年度预测.worksheets[0]

wb_滚动预测 = openpyxl.load_workbook(
    r"\\192.168.70.101\19计划管理部\01.计划\3，物料需求\3，每月追料\1.销售每月需求\2025.04-2025.06月销售预测需求\2025.4-6月销售滚动预测需求明细表.xlsx")
ws_滚动预测 = wb_滚动预测.worksheets[0]

wb_C = openpyxl.load_workbook(r"C:\Users\10352\Desktop\C类判定.xlsx")
ws_C = wb_C.worksheets[0]

bom_尾阶 = tools_pmc.Y状态BOM尾阶用量_pro()[1]

for i in range(2, ws_C.max_row + 1):
    list_sn = []
    for j in bom_尾阶:
        if ws_C.cell(i, 1).value in bom_尾阶[j] and j[0] == "3":
            list_sn.append(j)
    if len(list_sn) > 0:
        ws_C.cell(row=i, column=2, value=str(list_sn))
        temp = 0
        for sn in list_sn:
            temp += ws_mc.cell(dict_mc_sn[sn], 37).value
        ws_C.cell(row=i, column=3, value=temp)

        temp = 0
        for sn in list_sn:
            temp += ws_mc.cell(dict_mc_sn[sn], 38).value
        ws_C.cell(row=i, column=4, value=temp)

        temp = 0
        for j in range(2, ws_年度预测.max_row + 1):
            if str(ws_年度预测.cell(j, 1).value) in list_sn:
                temp += ws_年度预测.cell(j, 4).value
        ws_C.cell(row=i, column=5, value=temp)

        temp = 0
        for j in range(2, ws_滚动预测.max_row + 1):
            if str(ws_滚动预测.cell(j, 1).value) in list_sn:
                temp += ws_滚动预测.cell(j, 4).value
        ws_C.cell(row=i, column=6, value=temp)

        list_规格 = []
        for sn in list_sn:
            list_规格.append(ws_mc.cell(dict_mc_sn[sn], 2).value)
        list_规格 = list(set(list_规格))

        temp1 = False
        temp = ""
        if len(list_规格) < 3:
            for 规格 in list_规格:
                temp += 规格 + "、"
            temp = temp[:-1]
            temp += "使用"
        else:
            temp = "通用物料"


        if ws_C.cell(i, 3).value > 0:
            temp += "，24年合计销货量" + str(ws_C.cell(i, 3).value) + "pcs"
            temp1 = True
        if ws_C.cell(i, 4).value > 0:
            temp += "，25年合计销货量" + str(ws_C.cell(i, 4).value) + "pcs"
            temp1 = True
        if ws_C.cell(i, 5).value > 0:
            temp += "，2025年合计年度预测量" + str(ws_C.cell(i, 5).value) + "pcs"
            temp1 = True
        if ws_C.cell(i, 6).value > 0:
            temp += "，2025年合计滚动预测量" + str(ws_C.cell(i, 6).value) + "pcs"
            temp1 = True
        ws_C.cell(row=i, column=8, value=temp)
        if temp1:
            ws_C.cell(row=i, column=7, value="可用")
            if ws_C.cell(i, 3).value+ws_C.cell(i, 4).value+ws_C.cell(i, 5).value+ws_C.cell(i, 6).value < ws_mc.cell(dict_mc_sn[ws_C.cell(i, 1).value], 12).value*0.5\
                    and ws_C.cell(i, 3).value+ws_C.cell(i, 4).value+ws_C.cell(i, 5).value+ws_C.cell(i, 6).value < 200:
                ws_C.cell(row=i, column=7, value="可用，需求少")
        else:
            ws_C.cell(row=i, column=7, value="停用呆滞")



wb_C.save(r"C:\Users\10352\Desktop\C类判定1.xlsx")
